package com.topisv.tms.repository.stock;

import java.util.List;

import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.data.repository.query.Param;

import com.topisv.tms.entity.stock.ViewCusStock;

public interface ViewCusStockDao extends PagingAndSortingRepository<ViewCusStock, Long>, JpaSpecificationExecutor<ViewCusStock> {

	@Query("select t from ViewCusStock t where t.customer.customerCode=?1 and t.warehouseCode=?2 and sku=?3")
	ViewCusStock loadStockByCondition(String customerCode,String warehouseCode, String sku);
	
	@Query("select t from ViewCusStock t where t.customer.customerCode=?1 and t.warehouseCode=?2 and bar=?3")
	List<ViewCusStock> findStockByCondition(String customerCode,String warehouseCode, String code);
	
    @Query(nativeQuery=true, value="SELECT CAST((@i\\:=@i+1) as char) as i, a.customer_name, a.group_name , a.sku, a.bar, a.goods_name, " +
            " a.sales_num, a.unused_num, a.order_num, a.locked_num, a.transport_num, a.air_num, a.total_num, a.sth " +
            " FROM (" +
            " SELECT " +
            "   cu.customer_name," +
            "   wh.group_name," +
            "   s.sku AS sku," +
            "   e.sku_bar AS bar," +
            "  e.sku_name AS goods_name,    " +
            "   sum(s.sales_num) AS sales_num," +
            "   sum(s.unused_num) AS unused_num," +
            "   sum(s.order_num) AS order_num," +
            "   sum(s.locked_num) AS locked_num," +
            "   sum(s.transport_num) AS transport_num," +
            "   sum(s.air_num) AS air_num," +
            "   (" +
            "       sum(s.sales_num) + sum(s.unused_num) + sum(s.order_num) + sum(s.locked_num) + sum(s.transport_num) + sum(s.air_num)" +
            "   ) AS total_num, null as sth " +
            "FROM cus_stock s " +
            "LEFT JOIN cus_stock_safe sf ON (" +
            "   sf.sku = s.sku AND sf.customer_code = s.customer_code AND sf.warehouse_code = s.warehouse_code" +
            ") " +
            "LEFT JOIN edi_goods_sku e ON (" +
            "   e.sku_id = s.sku AND e.customer_code = s.customer_code" +
            ") " +
            "LEFT JOIN customers cu ON cu.customer_code=s.customer_code " +
            "LEFT JOIN pms_areagroup wh on wh.group_code=s.warehouse_code " +
            "WHERE s.customer_code in (:customerCodes) " +
            " AND (:warehouseCode = '' or :warehouseCode is null or s.warehouse_code= :warehouseCode) " +
            " AND (:sku='' or :sku is null or s.sku like :sku)" +
            " GROUP BY " +
            "   wh.group_name," +
            "   cu.customer_name," +
            "   s.sku, e.sku_bar, e.sku_name" +
            ") a, (select   @i\\:=0) b")
    List<Object[]> exportStockBySku(@Param("customerCodes") List<String> customerCodes, @Param("warehouseCode") String warehouseCode, @Param("sku") String sku);
}